﻿
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Model;
namespace Dal
{
    public class DalLayer
    {
        /// <summary>
        /// 用于处理null值
        /// </summary>
        /// <param name="obj"></param>
        /// <returns></returns>
        static private object SqlNull(object obj)
        {
            if (obj == null)
                return DBNull.Value;
            else if(obj.GetType() == typeof(string))
            {
                if(obj.ToString() == "")
                    return DBNull.Value;
            }
            return obj;
        }
        /// <summary>
        /// 创建一个用户
        /// </summary>
        /// <param name="user"></param>
        /// <returns>添加成功的个数</returns>
        public int SetUserInfo(List<User_info> user)
        {
            int count = 0;
            string sql = "insert into User_info(SID,account,pwd,name,wallet,goods,face,is_accepted,email,acceptpicture)values(@sid,@account,@pwd,@name,@wallet,@goods,@face,@is_accepted,@email,@acceptpicture)";
            foreach(User_info i in user)
            {
                SqlParameter[] paras = new SqlParameter[]{
                    new SqlParameter("@sid",SqlNull(i.SID)),
                    new SqlParameter("@account",SqlNull(i.account)),
                    new SqlParameter("@pwd",SqlNull(i.pwd)),
                    new SqlParameter("@name",SqlNull(i.name)),
                    new SqlParameter("@wallet",SqlNull(i.wallet)),
                    new SqlParameter("@goods",SqlNull(i.goods)),
                    new SqlParameter("@face",SqlNull(i.face)),
                    new SqlParameter("@is_accepted",SqlNull(i.is_accepted)),
                    new SqlParameter("@email",SqlNull(i.email)),
                    new SqlParameter("@acceptpicture",SqlNull(i.acceptpicture))
                };
                try
                {
                    if (SqlHelper.ExecuteNonQuery(sql, paras) >= 1)
                    {
                        count++;
                    }
                   
                }catch(Exception ex)
                {
                    //此处可以做异常处理记录
                }
                
            }
            return count;
        }
        /// <summary>
        /// 删除某个用户
        /// </summary>
        /// <param name="SID">学号</param>
        /// <returns>1表示成功，0表示失败</returns>
        public int DeleteUserInfo(string SID)
        {
            string sql = "delete from User_info where SID=@sid";
            SqlParameter para = new SqlParameter("@sid", SID);
            try
            {
                if (SqlHelper.ExecuteNonQuery(sql, para) >= 1)
                {
                    return 1;
                }
            }
            catch (Exception ex)
            {
                //异常处理
            }
            return 0;
        }
        /// <summary>
        /// 更新用户信息
        /// </summary>
        /// <param name="user"></param>
        /// <returns>0表示失败 1表示成功</returns>
        public int UpdateUserInfo(User_info user)
        {
            string sql = "update User_info set account=@account,pwd=@pwd,name=@name,wallet=@wallet,goods=@goods,face=@face,is_accepted=@is_accepted,email=@email,acceptpicture=@acceptpicture where SID=@SID";
            SqlParameter[] paras = new SqlParameter[]
            {
                    new SqlParameter("@sid",SqlNull(user.SID)),
                    new SqlParameter("@account",SqlNull(user.account)),
                    new SqlParameter("@pwd",SqlNull(user.pwd)),
                    new SqlParameter("@name",SqlNull(user.name)),
                    new SqlParameter("@wallet",SqlNull(user.wallet)),
                    new SqlParameter("@goods",SqlNull(user.goods)),
                    new SqlParameter("@face",SqlNull(user.face)),
                    new SqlParameter("@is_accepted",SqlNull(user.is_accepted)),
                    new SqlParameter("@email",SqlNull(user.email)),
                    new SqlParameter("@acceptpicture",SqlNull(user.acceptpicture))
            };
            try
            {
                if (SqlHelper.ExecuteNonQuery(sql, paras) >= 1)
                {
                    return 1;
                }
            }catch(Exception ex)
            {
                //异常处理
            }
            return 0;
        }
        /// <summary>
        /// 获得用户对象
        /// </summary>
        /// <param name="SID">需要查询的用户的学号</param>
        /// <returns>用户对象，当用户对象的账号为"Error"时出现错误 不存在时返回"Null"</returns>
        public User_info GetUserInfo(string SID)
        {
            string sql = "select * from User_info where SID=@SID";
            SqlParameter para = new SqlParameter("@SID", SID);
            User_info user = new User_info();
            try
            {
                DataTable dt = SqlHelper.ExecuteDataTable(sql,para);
                user = DataTableToUseInfo(dt)[0];
            }catch(ArgumentOutOfRangeException ex)
            {
                user.account = "Null";
            }
            catch(Exception ex)
            {
                user.account = "Error";
                
            }
            return user;
        }
        /// <summary>
        /// 获得用户对象
        /// </summary>
        /// <param name="account">需要查询的用户的账号</param>
        /// <returns>用户对象，当用户对象的账号为"Error"时出现错误 不存在时返回"Null"</returns>
        public User_info GetUserInfo(string account,int k)
        {
            string sql = "select * from User_info where account=@account";
            SqlParameter para = new SqlParameter("@account", account);
            User_info user = new User_info();
            try
            {
                DataTable dt = SqlHelper.ExecuteDataTable(sql, para);
                user = DataTableToUseInfo(dt)[0];
            }
            catch (ArgumentOutOfRangeException ex)
            {
                user.account = "Null";
            }
            catch (Exception ex)
            {
                user.account = "Error";

            }
            return user;
        }
        /// <summary>
        /// 获取所有用户对象
        /// </summary>
        /// <returns></returns>
        public List<User_info> GetUser_Infos()
        {
            string sql = "select * from User_info";
            try
            {
                return DataTableToUseInfo(SqlHelper.ExecuteDataTable(sql));
            }
            catch(Exception ex)
            {
                return null;
            }
        }
        /// <summary>
        /// 添加需要认证的记录
        /// </summary>
        /// <param name="user"></param>
        /// <returns></returns>
        public int SetIdetify(List<User_info> user)
        {
            int count = 0;
            string sql = "insert into Identify(SID,account,pwd,name,wallet,goods,face,is_accepted,email,acceptpicture)values(@sid,@account,@pwd,@name,@wallet,@goods,@face,@is_accepted,@email,@acceptpicture)";
            foreach (User_info i in user)
            {
                SqlParameter[] paras = new SqlParameter[]{
                    new SqlParameter("@sid",SqlNull(i.SID)),
                    new SqlParameter("@account",SqlNull(i.account)),
                    new SqlParameter("@pwd",SqlNull(i.pwd)),
                    new SqlParameter("@name",SqlNull(i.name)),
                    new SqlParameter("@wallet",SqlNull(i.wallet)),
                    new SqlParameter("@goods",SqlNull(i.goods)),
                    new SqlParameter("@face",SqlNull(i.face)),
                    new SqlParameter("@is_accepted",SqlNull(i.is_accepted)),
                    new SqlParameter("@email",SqlNull(i.email)),
                    new SqlParameter("@acceptpicture",SqlNull(i.acceptpicture))
                };
                try
                {
                    if (SqlHelper.ExecuteNonQuery(sql, paras) >= 1)
                    {
                        count++;
                    }

                }
                catch (Exception ex)
                {
                    //此处可以做异常处理记录
                }

            }
            return count;
        }
        /// <summary>
        /// 删除认证信息
        /// </summary>
        /// <param name="SID"></param>
        /// <returns></returns>
        public int DeleteIdentify(string SID)
        {
            string sql = "delete from Identify where SID=@sid";
            SqlParameter para = new SqlParameter("@sid", SID);
            try
            {
                if (SqlHelper.ExecuteNonQuery(sql, para) >= 1)
                {
                    return 1;
                }
            }
            catch (Exception ex)
            {
                //异常处理
            }
            return 0;
        }
        /// <summary>
        /// 认证信息更新
        /// </summary>
        /// <param name="user"></param>
        /// <returns></returns>
        public int UpdateIdentify(User_info user)
        {
            string sql = "update Identify set account=@account,pwd=@pwd,name=@name,wallet=@wallet,goods=@goods,face=@face,is_accepted=@is_accepted,email=@email,acceptpicture=@acceptpicture where SID=@SID";
            SqlParameter[] paras = new SqlParameter[]
            {
                    new SqlParameter("@sid",SqlNull(user.SID)),
                    new SqlParameter("@account",SqlNull(user.account)),
                    new SqlParameter("@pwd",SqlNull(user.pwd)),
                    new SqlParameter("@name",SqlNull(user.name)),
                    new SqlParameter("@wallet",SqlNull(user.wallet)),
                    new SqlParameter("@goods",SqlNull(user.goods)),
                    new SqlParameter("@face",SqlNull(user.face)),
                    new SqlParameter("@is_accepted",SqlNull(user.is_accepted)),
                    new SqlParameter("@email",SqlNull(user.email)),
                    new SqlParameter("@acceptpicture",SqlNull(user.acceptpicture))
            };
            try
            {
                if (SqlHelper.ExecuteNonQuery(sql, paras) >= 1)
                {
                    return 1;
                }
            }
            catch (Exception ex)
            {
                //异常处理
            }
            return 0;
        }
        /// <summary>
        /// 获得认证信息
        /// </summary>
        /// <param name="SID"></param>
        /// <returns></returns>
        public User_info GetIdentify(string SID)
        {
            string sql = "select * from Identify where SID=@SID";
            SqlParameter para = new SqlParameter("@SID", SID);
            User_info user = new User_info();
            try
            {
                DataTable dt = SqlHelper.ExecuteDataTable(sql, para);
                user = DataTableToUseInfo(dt)[0];
            }
            catch (ArgumentOutOfRangeException ex)
            {
                user.account = "Null";
            }
            catch (Exception ex)
            {
                user.account = "Error";

            }
            return user;
        }
        /// <summary>
        /// 获取所有认证信息
        /// </summary>
        /// <returns></returns>
        public List<User_info> GetIdentify()
        {
            string sql = "select * from Identify";
            try
            {
                return DataTableToUseInfo(SqlHelper.ExecuteDataTable(sql));
            }
            catch (Exception ex)
            {
                return null;
            }
        }

        /// <summary>
        /// 创建商品
        /// </summary>
        /// <param name="list">商品列表</param>
        /// <returns>成功创建的个数</returns>
        public int SetGoodsInfo(List<Goods_info> list)
        {
            int count = 0;
            string sql = "insert into Goods_info (cover,owner,description,price,count,state) values(@cover,@owner,@description,@price,@count,@state)";
            foreach(Goods_info good in list)
            {
                SqlParameter[] paras = new SqlParameter[] { 
                    new SqlParameter("@cover",SqlNull(good.cover)),
                    new SqlParameter("@owner",SqlNull(good.owner)),
                    new SqlParameter("@description",SqlNull(good.description)),
                    new SqlParameter("@price",SqlNull(good.price)),
                    new SqlParameter("@count",SqlNull(good.count)),
                    new SqlParameter("@state",SqlNull(good.state))
                };
                try
                {
                    if(SqlHelper.ExecuteNonQuery(sql, paras)>=1)
                    count++;
                }
                catch (Exception ex)
                {

                }                                  
            }
          return count;
        }

        /// <summary>
        /// 删除商品
        /// </summary>
        /// <param name="Serial_Num">需要删除的商品编号</param>
        /// <returns>成功返回1，失败返回0</returns>
        public int DeleteGoodsInfo(int Serial_Num)
        {
            string sql = "delete from Goods_info where Serial_Num = @Serial_Num";
            SqlParameter para = new SqlParameter("@Serial_Num", Serial_Num);
            try
            {
                return SqlHelper.ExecuteNonQuery(sql, para);
            }catch(Exception ex)
            {
                return 0;
            }
        }
        /// <summary>
        /// 更新商品信息
        /// </summary>
        /// <param name="good"></param>
        /// <returns>1表示成功 0表示失败</returns>
        public int UpdateGoodsInfo(Goods_info good)
        {
            string sql = "update Goods_info set cover=@cover,owner=@owner,description=@description,price=@price,count=@count,state=@state where Serial_Num=@Serial_Num";
            SqlParameter[] paras = new SqlParameter[]
            {
                    new SqlParameter("@cover",SqlNull(good.cover)),
                    new SqlParameter("@owner",SqlNull(good.owner)),
                    new SqlParameter("@description",SqlNull(good.description)),
                    new SqlParameter("@price",SqlNull(good.price)),
                    new SqlParameter("@count",SqlNull(good.count)),
                    new SqlParameter("@state",SqlNull(good.state))
            };
            try
            {
               return  SqlHelper.ExecuteNonQuery(sql, paras);
            }catch(Exception ex){
                return 0;
            }
        }
        /// <summary>
        /// 获取商品信息
        /// </summary>
        /// <param name="Serial_Num">商品编号</param>
        /// <returns>商品信息，若商品信息对象的owner属性为"Error"时获取失败 不存在时为"Null"</returns>
        public Goods_info GetGoodsInfo(int Serial_Num)
        {
            string sql = "select * from Goods_info where Serial_Num=@Serial_Num";
            SqlParameter para = new SqlParameter("@Serial_Num",Serial_Num);
            Goods_info good = new Goods_info();
            try
            {
                DataTable dt = SqlHelper.ExecuteDataTable(sql,para);
                good = DataTableToGoodsInfo(dt)[0];
            }catch(ArgumentOutOfRangeException ex)
            {
                good.owner = "Null";
            }
            catch(Exception ex)
            {
                good.owner = "Error";
            }
            
            return good;
            
           
        }
        /// <summary>
        /// 获取所有商品信息
        /// </summary>
        /// <returns></returns>
        public List<Goods_info> GetGoodsInfos()
        {
            string sql = "select * from Goods_info ";
            try
            {
                return DataTableToGoodsInfo(SqlHelper.ExecuteDataTable(sql));
            }catch(Exception ex)
            {
                return null;
            }
        }
        /// <summary>
        /// 添加购物车
        /// </summary>
        /// <param name="cart"></param>
        /// <returns>返回添加成功的个数</returns>
        public int SetCart(List<Cart> list)
        {
            int count = 0;
            string sql = "insert into Cart (good,owner,count) values(@good,@owner,@count)";
            foreach(Cart cart in list)
            {
                SqlParameter[] paras = new SqlParameter[]
                {
                new SqlParameter("@good",SqlNull(cart.good)),
                new SqlParameter("@owner",SqlNull(cart.owner)),
                new SqlParameter("@count",SqlNull(cart.count))
                };
                try
                {
                    if(SqlHelper.ExecuteNonQuery(sql, paras) >= 1)
                    count++;
                }
                catch (Exception ex)
                {
                   
                }
            }
        return count;
        }

        /// <summary>
        /// 删除购物车
        /// </summary>
        /// <param name="ID"></param>
        /// <returns></returns>
        public int DeleteCart(int ID)
        {
            string sql = "delete from Cart where ID=@ID";
            SqlParameter para = new SqlParameter("@ID", ID);
            try
            {

                return SqlHelper.ExecuteNonQuery(sql, para);
            }catch(Exception ex)
            {
                return 0;
            }
        }
        /// <summary>
        /// 更新购物车
        /// </summary>
        /// <param name="cart"></param>
        /// <returns>1成功 0失败</returns>
        public int UpdateCart(Cart cart)
        {
            string sql = "update Cart set count=@count,owner=@owner,good=@good where ID=@ID";
            SqlParameter[] paras = new SqlParameter[]
            {
                new SqlParameter("@good",SqlNull(cart.good)),
                new SqlParameter("@owner",SqlNull(cart.owner)),
                new SqlParameter("@count",SqlNull(cart.count)),
                new SqlParameter ("@ID",SqlNull(cart.ID))
            };
            try
            {
                return SqlHelper.ExecuteNonQuery(sql, paras);
            }catch(Exception ex)
            {
                return 0;
            }

        }

        /// <summary>
        /// 根据ID获取购物车信息
        /// </summary>
        /// <param name="ID"></param>
        /// <returns>购物车对象 若该对象的owner为"Error" 则获取失败 不存在时返回"Null"</returns>
        public Cart GetCart(int ID)
        {
            string sql = "select * from Cart where ID=@ID";
            SqlParameter para = new SqlParameter("@ID", ID);
            Cart cart = new Cart();
            try
            {
                DataTable dt = SqlHelper.ExecuteDataTable(sql, para);
                return DataTableTOCart(dt)[0];
            }catch(ArgumentOutOfRangeException ex)
            {
                cart.owner = "Null";
                return cart;
            }
            catch(Exception ex)
            {
                cart.owner = "Error";
                return cart;
            }
        }
        /// <summary>
        /// 根据用户学号获取购物车信息
        /// </summary>
        /// <param name="owner"></param>
        /// <returns>购物车列表</returns>
        public List<Cart> GetCart(string owner)
        {
            string sql = "select * from Cart where owner=@owner";
            SqlParameter para = new SqlParameter("@owner", owner);
            try
            {
               DataTable dt = SqlHelper.ExecuteDataTable(sql, para);
                return DataTableTOCart(dt);
            }catch(Exception ex)
            {
                return null;
            }
        }
        /// <summary>
        /// 创建订单
        /// </summary>
        /// <param name="list"></param>
        /// <returns>创建成功的数量</returns>
        public int SetOrder(List<Order> list)
        {
            int count = 0;
            string sql = "insert into Orders (owner,goods,amount,state) values(@owner,@goods,@amount,@state)";
            foreach(Order or in list)
            {
                SqlParameter[] paras = new SqlParameter[]
                {
                    new SqlParameter("@owner",SqlNull(or.owner)),
                    new SqlParameter("@goods",SqlNull(or.goods)),
                    new SqlParameter("@amount",SqlNull(or.amount)),
                    new SqlParameter("@state",SqlNull(or.state))
                };
                try
                {
                    count+=SqlHelper.ExecuteNonQuery(sql, paras);
                }catch(Exception ex)
                {

                }
            }
            return count;
        }
        /// <summary>
        /// 删除订单
        /// </summary>
        /// <param name="ID"></param>
        /// <returns>1成功 0失败</returns>
        public int DeleteOrder(int ID)
        {
            string sql = "delete from Orders where ID=@ID";
            SqlParameter para = new SqlParameter("@ID", ID);
            try
            {
                return SqlHelper.ExecuteNonQuery(sql, para);
            }
            catch
            {
                return 0;
            }
        }

        /// <summary>
        /// 订单更新
        /// </summary>
        /// <param name="or"></param>
        /// <returns>1成功 0失败</returns>
        public int UpdateOrder(Order or)
        {
            string sql = "update Orders set owner=@owner,goods=@goods,amount=@amount,state=@state where ID=@ID";
            SqlParameter[] paras = new SqlParameter[]
            {
                    new SqlParameter("@owner",SqlNull(or.owner)),
                    new SqlParameter("@goods",SqlNull(or.goods)),
                    new SqlParameter("@amount",SqlNull(or.amount)),
                    new SqlParameter("@state",SqlNull(or.state)),
                      new SqlParameter("@ID",SqlNull(or.ID))
            };
            try
            {
                return SqlHelper.ExecuteNonQuery(sql, paras);
            }catch(Exception ex)
            {
                return 0;
            }
        }
        /// <summary>
        /// 获取订单信息
        /// </summary>
        /// <param name="ID"></param>
        /// <returns></returns>
        public Order GetOrder(int ID)
        {
            string sql = "select * from Orders where ID=@ID";
            SqlParameter para = new SqlParameter("@ID", ID);
            try
            {
               DataTable dt = SqlHelper.ExecuteDataTable(sql, para);
                return DataTableToOrder(dt)[0];
            }
            catch(Exception ex)
            {
                return null;
            }
        }
        public List<Order> GetOrder(string owner)
        {
            string sql = "select * from Orders where owner=@owner";
            SqlParameter para = new SqlParameter("@owner", owner);
            try
            {
               DataTable dt = SqlHelper.ExecuteDataTable(sql, para);
                return DataTableToOrder(dt);

            }
            catch(Exception ex)
            {
                return null;
            }
        }
        private List<User_info> DataTableToUseInfo(DataTable dt)
        {
            List<User_info> list = new List<User_info>();
            foreach(DataRow dr in dt.Rows)
            {   User_info user = new User_info();
                user.SID = dr[0].ToString();
                user.account = dr[1].ToString();
                user.pwd = dr[2].ToString();
                user.name = dr[3].ToString();
                user.wallet = float.Parse(dr[4].ToString());
                user.goods = dr[5].ToString();
                user.face = dr[6].ToString();
                user.is_accepted = int.Parse(dr[7].ToString());
                user.regtime = DateTime.Parse(dr[8].ToString());
                user.email = dr[9].ToString();
                user.acceptpicture = dr[10].ToString();
                list.Add(user);
            }
            return list;
        }
        private List<Goods_info> DataTableToGoodsInfo(DataTable dt)
        {
            List<Goods_info> list = new List<Goods_info>();
            foreach (DataRow dr in dt.Rows)
            {
                Goods_info good = new Goods_info();
                good.Serial_Num= int.Parse(dr[0].ToString());
                good.cover = dr[1].ToString();
                good.owner = dr[2].ToString();
                good.description = dr[3].ToString();
                good.price  = float.Parse(dr[4].ToString());
                good.count = int.Parse(dr[5].ToString());
                good.state =int.Parse( dr[6].ToString());
                good.addtime = DateTime.Parse(dr[7].ToString());
                list.Add(good);
            }
            return list;
        }
        private List<Cart> DataTableTOCart(DataTable dt)
        {
            List<Cart> list = new List<Cart>();
            foreach(DataRow dr in dt.Rows)
            {
                Cart cart = new Cart();
                cart.ID = int.Parse(dr[0].ToString());
                cart.good = int.Parse(dr[1].ToString());
                cart.owner = dr[2].ToString();
                cart.count = int.Parse(dr[3].ToString());
                cart.addtime = DateTime.Parse(dr[4].ToString());
                list.Add(cart);
       
            }
            return list;
        }
        private List<Order> DataTableToOrder(DataTable dt)
        {
            List<Order> list = new List<Order>();
            foreach(DataRow dr in dt.Rows)
            {
                Order or = new Order();
                or.ID = int.Parse(dr[0].ToString());
                or.owner = dr[1].ToString();
                or.goods = dr[2].ToString();
                or.amount = float.Parse(dr[3].ToString());
                or.state = int.Parse(dr[4].ToString());
                or.addtime = DateTime.Parse(dr[5].ToString());
                list.Add(or);
            }
            return list;
        }
    }
    
}